package cn.edu.zhku.citrusmonitor.baseinfo.pest.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


import cn.edu.zhku.citrusmonitor.domain.Bean;
import cn.edu.zhku.citrusmonitor.domain.TbPest;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;
import cn.edu.zhku.citrusmonitor.util.StringUtil;

public class PestDaoImpl implements PestDao{
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Long getCount() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select count(*) from pest";
		long l = (long) qr.query(sql, new ScalarHandler(1));
		return l;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<Bean> getPestBeanList(Integer currentPage, Integer pageSize) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select pest_id as id,pest_name as title,create_time as time from pest";
		ArrayList param = new ArrayList();
		
		sql += " limit ?,?";
		param.add((currentPage-1)*pageSize);
		param.add(pageSize);
		List<Bean> list = qr.query(sql, new BeanListHandler<Bean>(Bean.class,processor), param.toArray());
		return list;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<TbPest> getPestDetailBeanList(Integer currentPage, Integer pageSize) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select pest_id,pest_name,content,category,distribution,img_path,create_time from pest";
		ArrayList param = new ArrayList();
		sql += " limit ?,?";
		param.add((currentPage-1)*pageSize);
		param.add(pageSize);
		List<TbPest> list = qr.query(sql, new BeanListHandler<TbPest>(TbPest.class,processor), param.toArray());
		return list;
	}
	
	@Override
	public List<TbPest> getPestById(String pestId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql  = "select pest_id,pest_name,content,category,distribution,img_path,create_time from pest where pest_id=?";
		List<TbPest> list = qr.query(sql, new BeanListHandler<TbPest>(TbPest.class,processor), pestId);
		return list;
	}

	@Override
	public int addPestByPestBean(TbPest pest) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "insert into pest(pest_id,pest_name,content,category,distribution,img_path) values(?,?,?,?,?,?)";
		return qr.update(sql,pest.getPestId(), pest.getPestName(), pest.getContent(), pest.getDistribution(), pest.getDistribution(), pest.getImgPath());
	}

	@Override
	public TbPest selectById(String pestId) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from pest where pest_id=?";
		
		return qr.query(sql, new BeanHandler<TbPest>(TbPest.class,processor), pestId);
	}

	@Override
	public int delectPestByPestBean(TbPest pest) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from pest where pest_id=?";
		
		return qr.update(sql, pest.getPestId());
	}

	@Override
	public int modifyPestByPestBean(TbPest pest) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		List<Object> param=new ArrayList<>();
		String sql = "update pest set pest_id=?";
		param.add(pest.getPestId());
		//修改的字段
		if(StringUtil.isNotEmpty(pest.getPestName())) {	
			sql +=",pest_name=?";
			param.add(pest.getPestName());
		}
		if(StringUtil.isNotEmpty(pest.getContent())) {
			sql +=",content=?";
			param.add(pest.getContent());
		}if(StringUtil.isNotEmpty(pest.getCategory())) {
			sql +=",category=?";
			param.add(pest.getCategory());
		}if(StringUtil.isNotEmpty(pest.getDistribution())) {
			sql +=",distribution=?";
			param.add(pest.getDistribution());
		}if(StringUtil.isNotEmpty(pest.getImgPath())) {
			sql +=",img_path=?";
			param.add(pest.getImgPath());
		}
		
		sql +="where pest_id=?";
		param.add(pest.getPestId());
		return qr.update(sql, param.toArray());
	}

	@Override
	public List<Bean> getRecentPestBeanList(Integer pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql  = "select pest_id as id,pest_name as title,create_time as time from pest order by create_time desc limit ?";
		List<Bean> list = qr.query(sql, new BeanListHandler<Bean>(Bean.class,processor), pageSize);
		return list;
	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<TbPest> searchPestByWord(Integer currentPage, Integer pageSize, String word) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from pest where";
		ArrayList param=new ArrayList();
		sql+=" pest_name like ?";
		param.add("%"+word+"%");
		sql+=" or distribution like ?";
		param.add("%"+word+"%");
		sql+=" or content like ?";
		param.add("%"+word+"%");
		sql+=" or category like ?";
		param.add("%"+word+"%");
		sql+=" limit ?,?";
		param.add((currentPage-1)*pageSize);
		param.add(pageSize);
		List<TbPest> list = qr.query(sql, new BeanListHandler<TbPest>(TbPest.class,processor), param.toArray());
		return list;
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Long searchPestCountByWord(String word) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select count(*) from pest where";
		ArrayList param=new ArrayList();
		sql+=" pest_name like ?";
		param.add("%"+word+"%");
		sql+=" or distribution like ?";
		param.add("%"+word+"%");
		sql+=" or content like ?";
		param.add("%"+word+"%");
		sql+=" or category like ?";
		param.add("%"+word+"%");
		Long l = (Long) qr.query(sql, new ScalarHandler(1), param.toArray());
		return l;
	}

	public Bean showPestById(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql  = "select pest_id as id,pest_name as title,content as content,distribution as distribution,img_path as file_path,create_time as time from pest where pest_id=?";
		Bean bean = qr.query(sql, new BeanHandler<Bean>(Bean.class,processor), id);
		return bean;
	}

	@Override
	public int[] delSelect(String[] ids) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from pest where pest_id=?";
		Object[][] params = new Object[ids.length][1];	//高维也就是行数 确定执行sql语句的次数，低维也就是列数是给?赋值
		
		for(int i = 0; i < ids.length; i++) {	//循环行数,决定SQL语句执行的次数
			params[i][0] = ids[i];	//给低维也就是列数“？”赋值，每行只给一列赋值，决定每条SQL语句的参数个数
		}
		return qr.batch(sql, params);
	}
}
